<!DOCTYPE html>



  


<html class="theme-next gemini use-motion" lang="zh-CN">
<head>
  <meta charset="UTF-8"/>
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1"/>
<meta name="theme-color" content="#222">









<meta http-equiv="Cache-Control" content="no-transform" />
<meta http-equiv="Cache-Control" content="no-siteapp" />
















  
  
  <link href="/java-notes/lib/fancybox/source/jquery.fancybox.css?v=2.1.5" rel="stylesheet" type="text/css" />







<link href="/java-notes/lib/font-awesome/css/font-awesome.min.css?v=4.6.2" rel="stylesheet" type="text/css" />

<link href="/java-notes/css/main.css?v=5.1.4" rel="stylesheet" type="text/css" />


  <link rel="apple-touch-icon" sizes="180x180" href="/java-notes/images/apple-touch-icon-next.png?v=5.1.4">


  <link rel="icon" type="image/png" sizes="32x32" href="/java-notes/images/favicon-32x32-next.png?v=5.1.4">


  <link rel="icon" type="image/png" sizes="16x16" href="/java-notes/images/favicon-16x16-next.png?v=5.1.4">


  <link rel="mask-icon" href="/java-notes/images/logo.svg?v=5.1.4" color="#222">





  <meta name="keywords" content="sql," />










<meta name="description" content="MySQL必知必会一、查询数据 1. SELECT语句1.1 检索单个列1SELECT prod_name FROM products;  注意：MySQL是不区分大小写的，包括关键字和查询字段">
<meta property="og:type" content="article">
<meta property="og:title" content="sql必知必会">
<meta property="og:url" content="https://hitomeng.gitee.io/2020/04/10/sql%E5%BF%85%E7%9F%A5%E5%BF%85%E4%BC%9A/index.html">
<meta property="og:site_name" content="JavaNote">
<meta property="og:description" content="MySQL必知必会一、查询数据 1. SELECT语句1.1 检索单个列1SELECT prod_name FROM products;  注意：MySQL是不区分大小写的，包括关键字和查询字段">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="https://hitomeng.gitee.io/java-notes/2020/04/10/sql%E5%BF%85%E7%9F%A5%E5%BF%85%E4%BC%9A/C:%5CUsers%5CHitoM%5CDesktop%5C1113510-20181226155910408-1414424975.png">
<meta property="article:published_time" content="2020-04-10T00:41:46.000Z">
<meta property="article:modified_time" content="2020-04-10T00:43:32.264Z">
<meta property="article:author" content="HitoMeng">
<meta property="article:tag" content="sql">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="https://hitomeng.gitee.io/java-notes/2020/04/10/sql%E5%BF%85%E7%9F%A5%E5%BF%85%E4%BC%9A/C:%5CUsers%5CHitoM%5CDesktop%5C1113510-20181226155910408-1414424975.png">



<script type="text/javascript" id="hexo.configurations">
  var NexT = window.NexT || {};
  var CONFIG = {
    root: '/java-notes/',
    scheme: 'Gemini',
    version: '5.1.4',
    sidebar: {"position":"left","display":"post","offset":12,"b2t":false,"scrollpercent":false,"onmobile":false},
    fancybox: true,
    tabs: true,
    motion: {"enable":true,"async":false,"transition":{"post_block":"fadeIn","post_header":"slideDownIn","post_body":"slideDownIn","coll_header":"slideLeftIn","sidebar":"slideUpIn"}},
    duoshuo: {
      userId: '0',
      author: 'Author'
    },
    algolia: {
      applicationID: '',
      apiKey: '',
      indexName: '',
      hits: {"per_page":10},
      labels: {"input_placeholder":"Search for Posts","hits_empty":"We didn't find any results for the search: ${query}","hits_stats":"${hits} results found in ${time} ms"}
    }
  };
</script>



  <link rel="canonical" href="https://hitomeng.gitee.io/2020/04/10/sql必知必会/"/>





  <title>sql必知必会 | JavaNote</title>
  








<meta name="generator" content="Hexo 4.2.0"></head>

<body itemscope itemtype="http://schema.org/WebPage" lang="zh-CN">

  
  
    
  

  <div class="container sidebar-position-left page-post-detail">
    <div class="headband"></div>

    <header id="header" class="header" itemscope itemtype="http://schema.org/WPHeader">
      <div class="header-inner"><div class="site-brand-wrapper">
  <div class="site-meta ">
    

    <div class="custom-logo-site-title">
      <a href="/java-notes/"  class="brand" rel="start">
        <span class="logo-line-before"><i></i></span>
        <span class="site-title">JavaNote</span>
        <span class="logo-line-after"><i></i></span>
      </a>
    </div>
      
        <p class="site-subtitle">To be a better developer!</p>
      
  </div>

  <div class="site-nav-toggle">
    <button>
      <span class="btn-bar"></span>
      <span class="btn-bar"></span>
      <span class="btn-bar"></span>
    </button>
  </div>
</div>

<nav class="site-nav">
  

  
    <ul id="menu" class="menu">
      
        
        <li class="menu-item menu-item-home">
          <a href="/java-notes/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-home"></i> <br />
            
            Home
          </a>
        </li>
      
        
        <li class="menu-item menu-item-categories">
          <a href="/java-notes/categories/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-th"></i> <br />
            
            Categories
          </a>
        </li>
      
        
        <li class="menu-item menu-item-tags">
          <a href="/java-notes/tags/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-tags"></i> <br />
            
            Tags
          </a>
        </li>
      
        
        <li class="menu-item menu-item-archives">
          <a href="/java-notes/archives/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-archive"></i> <br />
            
            Archives
          </a>
        </li>
      
        
        <li class="menu-item menu-item-about">
          <a href="/java-notes/about/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-user"></i> <br />
            
            About
          </a>
        </li>
      

      
    </ul>
  

  
</nav>



 </div>
    </header>

    <main id="main" class="main">
      <div class="main-inner">
        <div class="content-wrap">
          <div id="content" class="content">
            

  <div id="posts" class="posts-expand">
    

  

  
  
  

  <article class="post post-type-normal" itemscope itemtype="http://schema.org/Article">
  
  
  
  <div class="post-block">
    <link itemprop="mainEntityOfPage" href="https://hitomeng.gitee.io/java-notes/2020/04/10/sql%E5%BF%85%E7%9F%A5%E5%BF%85%E4%BC%9A/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="name" content="HitoMeng">
      <meta itemprop="description" content="">
      <meta itemprop="image" content="/java-notes/images/avatar.gif">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="JavaNote">
    </span>

    
      <header class="post-header">

        
        
          <h1 class="post-title" itemprop="name headline">sql必知必会</h1>
        

        <div class="post-meta">
          <span class="post-time">
            
              <span class="post-meta-item-icon">
                <i class="fa fa-calendar-o"></i>
              </span>
              
                <span class="post-meta-item-text">Posted on</span>
              
              <time title="Post created" itemprop="dateCreated datePublished" datetime="2020-04-10T08:41:46+08:00">
                2020-04-10
              </time>
            

            

            
          </span>

          

          
            
          

          
          

          

          

          

        </div>
      </header>
    

    
    
    
    <div class="post-body" itemprop="articleBody">

      
      

      
        <h1 id="MySQL必知必会"><a href="#MySQL必知必会" class="headerlink" title="MySQL必知必会"></a>MySQL必知必会</h1><p>一、查询数据</p>
<h3 id="1-SELECT语句"><a href="#1-SELECT语句" class="headerlink" title="1. SELECT语句"></a>1. SELECT语句</h3><h4 id="1-1-检索单个列"><a href="#1-1-检索单个列" class="headerlink" title="1.1 检索单个列"></a>1.1 检索单个列</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT prod_name FROM products;</span><br></pre></td></tr></table></figure>

<p><strong>注意：MySQL是不区分大小写的，包括关键字和查询字段</strong></p>
<a id="more"></a>
<h4 id="1-2-检索多个列"><a href="#1-2-检索多个列" class="headerlink" title="1.2 检索多个列"></a>1.2 检索多个列</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT prod_name, prod_price FROM products;</span><br></pre></td></tr></table></figure>

<p>检索所有列：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT * FROM products;</span><br></pre></td></tr></table></figure>

<p><strong>最好不要使用  *，除非你真的是希望查询所有的字段</strong></p>
<h4 id="1-3-限制检索结果"><a href="#1-3-限制检索结果" class="headerlink" title="1.3 限制检索结果"></a>1.3 限制检索结果</h4><p>为了返回检索结果的第一行或前几行，可以通过LIMIT子句限制结果。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT prod_name FROM products LIMIT 5; # 取出结果的前五个数据</span><br></pre></td></tr></table></figure>

<p>当然也可以指定 <strong>开始行和行数：</strong></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT prod_name FROM products LIMIT 5, 5;# 从第五行开始，需要五个数据 index 5--&gt;9</span><br></pre></td></tr></table></figure>

<p><strong>注意：MySQL起始行是0，LIMIT 1, 1指的是第二行！</strong></p>
<h4 id="1-4-排序检索结果"><a href="#1-4-排序检索结果" class="headerlink" title="1.4 排序检索结果"></a>1.4 排序检索结果</h4><blockquote>
<p>ORDER BY 子句，根据需要排序检出数据。</p>
</blockquote>
<figure class="highlight plain"><figcaption><span>prod_name FROM products;```其实,检出的数据并不是完全随机的,而是按照底层表中出现的顺序显示.</span></figcaption><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"></span><br><span class="line">* ASC: 升序 没必要设置,因为这是缺省值</span><br><span class="line">* DESC: 降序 (从Z到A)</span><br><span class="line"></span><br><span class="line">&#96;&#96;&#96;mysql</span><br><span class="line">SELECT prod_id, prod_name FROM products ORDER BY prod_price DESC; # 按价格降序排列</span><br></pre></td></tr></table></figure>

<p>当然也是可以多列参与排序，而且可以为<strong>不同列指定不同的排序顺序</strong></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT prod_id, prod_name FROM products ORDER BY prod_price DESC, prod_name; # 按价格降序排列</span><br></pre></td></tr></table></figure>

<blockquote>
<p>区分大小写和排序顺序：A与a相同吗？是的，在字典排序中，A被视为与a相同。如果你想更改这种排序，需要请求DBA。</p>
</blockquote>
<h4 id="1-5-过滤数据"><a href="#1-5-过滤数据" class="headerlink" title="1.5 过滤数据"></a>1.5 过滤数据</h4><p>使用 <strong>WHERE</strong>子句可以指定搜索条件（search criteria）。</p>
<p>操作符如下</p>
<table>
<thead>
<tr>
<th>操作符</th>
<th>说明</th>
</tr>
</thead>
<tbody><tr>
<td>=</td>
<td>等于</td>
</tr>
<tr>
<td>&lt;&gt;</td>
<td>不等于</td>
</tr>
<tr>
<td>!=</td>
<td>不等于</td>
</tr>
<tr>
<td>&lt;</td>
<td>小于</td>
</tr>
<tr>
<td>&gt;</td>
<td>大于</td>
</tr>
<tr>
<td>&lt;=</td>
<td>小于等于</td>
</tr>
<tr>
<td>&gt;=</td>
<td>大于等于</td>
</tr>
<tr>
<td>BETWEEN</td>
<td>指定的两个值之间</td>
</tr>
</tbody></table>
<h5 id="1-5-1-检查单个值"><a href="#1-5-1-检查单个值" class="headerlink" title="1.5.1 检查单个值"></a>1.5.1 检查单个值</h5><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT prod_name FROM products WHERE prod_name &#x3D; &#39;fues&#39;;# mysql不区分大小写, Fues的行同样会返回</span><br></pre></td></tr></table></figure>

<h5 id="1-5-2-范围检查"><a href="#1-5-2-范围检查" class="headerlink" title="1.5.2 范围检查"></a>1.5.2 范围检查</h5><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT prod_name FROM products WHERE prod_price BETWEEN 5 AND 10; # 价格在5到10之间的所有产品</span><br></pre></td></tr></table></figure>

<h5 id="1-5-3-控制查询"><a href="#1-5-3-控制查询" class="headerlink" title="1.5.3 控制查询"></a>1.5.3 控制查询</h5><p>如果一个列不包含值，称其为 <strong>NULL</strong>。注意NULL与空是不用的概念的，<code>&#39;&#39;</code>是空但不是null。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT prod_name FROM products WHERE prod_name IS NULL; # 返回没有名称的所有产品，不包括 &#39;&#39;</span><br></pre></td></tr></table></figure>

<h5 id="1-5-4-OR操作符"><a href="#1-5-4-OR操作符" class="headerlink" title="1.5.4 OR操作符"></a>1.5.4 OR操作符</h5><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT prod_name, prod_price FROM products WHERE vend_id &#x3D; 1002 OR vend_id &#x3D; 1003;</span><br></pre></td></tr></table></figure>

<blockquote>
<p>WHERE可包含任意数目的AND和OR操作符，但是不同的写法可能会造成不同的计算顺序</p>
</blockquote>
<p>例如：希望找到价格为8.99和5.99以及价格在10以上的产品</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT prod_name, prod_price FROM Products WHERE prod_price &#x3D; 8.99 OR prod_price &#x3D; 5.99 AND prod_price &gt;&#x3D;10;</span><br></pre></td></tr></table></figure>

<p>可结果是如下，并没有价格超过10的，因为计算次序问题。<strong>AND 操作符的优先级大于 OR，所以注意加括号</strong></p>
<p>prod_name    prod_price<br>12 inch teddy bear    8.99</p>
<h5 id="1-5-5-IN-操作符"><a href="#1-5-5-IN-操作符" class="headerlink" title="1.5.5 IN 操作符"></a>1.5.5 IN 操作符</h5><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT XX FROM yy WHERE zz IN (A,B,C);</span><br></pre></td></tr></table></figure>

<p>IN操作符与OR是类似的，但是IN更加清晰直观。</p>
<h4 id="1-6-用通配符进行过滤"><a href="#1-6-用通配符进行过滤" class="headerlink" title="1.6 用通配符进行过滤"></a>1.6 用通配符进行过滤</h4><h5 id="1-6-1-LIKE操作符"><a href="#1-6-1-LIKE操作符" class="headerlink" title="1.6.1 LIKE操作符"></a>1.6.1 LIKE操作符</h5><ul>
<li><p>最常使用的通配符是 <strong>百分号%</strong>,表示任何字符出现任意次数。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT prod_id FROM products WHERE prod_name LIKE &quot;jet%&quot;; # 找到以jet开头的产品名称的ID</span><br></pre></td></tr></table></figure>

<blockquote>
<p>如果MYSQL配置了区分大小写（建表时，字段增加BINARY 属性），那么搜索也是匹配大小写的。</p>
</blockquote>
</li>
<li><p>下划线（_）通配符：匹配单个字符</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT prod_id FROM products WHERE prod_name LIKE &#39;_ ton anvil&#39;;</span><br></pre></td></tr></table></figure>

</li>
</ul>
<blockquote>
<p>正如所见，通配符很有用，但是搜索时间更长</p>
<ul>
<li>不要过度使用</li>
<li>除非绝对必要，否则不要把模糊搜索放在开始处</li>
<li>仔细注意通配符的位置，如果放错位置可能不会返回想要的结果</li>
</ul>
</blockquote>
<h4 id="1-7-使用正则表达式搜索"><a href="#1-7-使用正则表达式搜索" class="headerlink" title="1.7 使用正则表达式搜索"></a>1.7 使用正则表达式搜索</h4><p><a href="https://www.runoob.com/mysql/mysql-regexp.html" target="_blank" rel="noopener">菜鸟教程</a></p>
<h3 id="2-创建计算字段"><a href="#2-创建计算字段" class="headerlink" title="2. 创建计算字段"></a>2. 创建计算字段</h3><p>很多时候，直接查询出来的数据结果是需要处理后返回的，虽然这个操作也可以交给应用层来做，但是DBS会让APP更加简洁。</p>
<h4 id="2-1-拼接字段"><a href="#2-1-拼接字段" class="headerlink" title="2.1 拼接字段"></a>2.1 拼接字段</h4><p><strong>Concat()将值连结到一起构成单个值。</strong></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT CONCAT(prod_id,&quot;_&quot;,prod_desc) FROM Products WHERE prod_name LIKE &#39;%ueen 	doll&#39;;</span><br></pre></td></tr></table></figure>

<h4 id="2-2-去除空格"><a href="#2-2-去除空格" class="headerlink" title="2.2 去除空格"></a>2.2 去除空格</h4><p><strong>Trim() 剪切空格，还可以单边剪切 LTrim(),RTrim()</strong></p>
<h4 id="2-3-执行算术计算"><a href="#2-3-执行算术计算" class="headerlink" title="2.3 执行算术计算"></a>2.3 执行算术计算</h4><p>MySQL支持下表中的基本算术操作符</p>
<table>
<thead>
<tr>
<th>操作符</th>
<th>说明</th>
</tr>
</thead>
<tbody><tr>
<td>+</td>
<td>加</td>
</tr>
<tr>
<td>-</td>
<td>减</td>
</tr>
<tr>
<td>*</td>
<td>乘</td>
</tr>
<tr>
<td>/</td>
<td>除</td>
</tr>
</tbody></table>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT quantity * item_price AS expanded_price FROM OrderItems WHERE order_num &#x3D; 20005;</span><br></pre></td></tr></table></figure>

<h3 id="3-数据处理函数"><a href="#3-数据处理函数" class="headerlink" title="3. 数据处理函数"></a>3. 数据处理函数</h3><h4 id="3-1-字符串处理工具"><a href="#3-1-字符串处理工具" class="headerlink" title="3.1 字符串处理工具"></a>3.1 字符串处理工具</h4><table>
<thead>
<tr>
<th>函数</th>
<th>说明</th>
</tr>
</thead>
<tbody><tr>
<td>Left()</td>
<td>返回字符串左边的字符</td>
</tr>
<tr>
<td>Length()</td>
<td>返回字符串的长度</td>
</tr>
<tr>
<td>Locate()</td>
<td>超出字符串的子串</td>
</tr>
<tr>
<td>Lower()</td>
<td>转换为小写</td>
</tr>
</tbody></table>
<p><img src="/java-notes/2020/04/10/sql%E5%BF%85%E7%9F%A5%E5%BF%85%E4%BC%9A/C:%5CUsers%5CHitoM%5CDesktop%5C1113510-20181226155910408-1414424975.png" alt></p>
<h4 id="3-2-日期和时间处理函数"><a href="#3-2-日期和时间处理函数" class="headerlink" title="3.2 日期和时间处理函数"></a>3.2 日期和时间处理函数</h4><p>首先应该注意的是MySQL使用的日期格式，强烈推荐使用 *<em>yyyy-mm-dd *</em>的格式，其他日期格式可能也行，但是这是首选的。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select cust_id ,order_num FROM Orders WHERE order_date &#x3D; &#39;2004-01-12&#39;;</span><br></pre></td></tr></table></figure>

<p><strong>如果日期后由具体时间，而且并不是默认的00：00：00,你会发现上面这种写法是查询不到的，那么你就需要日期处理函数Date()</strong></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select cust_id ,order_num FROM Orders WHERE Date(order_date) &#x3D; &#39;2004-01-12&#39;;</span><br></pre></td></tr></table></figure>

<table>
<thead>
<tr>
<th align="left">函数</th>
<th align="left">描述</th>
</tr>
</thead>
<tbody><tr>
<td align="left"><a href="https://www.w3school.com.cn/sql/func_now.asp" target="_blank" rel="noopener">NOW()</a></td>
<td align="left">返回当前的日期和时间</td>
</tr>
<tr>
<td align="left"><a href="https://www.w3school.com.cn/sql/func_curdate.asp" target="_blank" rel="noopener">CURDATE()</a></td>
<td align="left">返回当前的日期</td>
</tr>
<tr>
<td align="left"><a href="https://www.w3school.com.cn/sql/func_curtime.asp" target="_blank" rel="noopener">CURTIME()</a></td>
<td align="left">返回当前的时间</td>
</tr>
<tr>
<td align="left"><a href="https://www.w3school.com.cn/sql/func_date.asp" target="_blank" rel="noopener">DATE()</a></td>
<td align="left">提取日期或日期/时间表达式的日期部分</td>
</tr>
<tr>
<td align="left"><a href="https://www.w3school.com.cn/sql/func_extract.asp" target="_blank" rel="noopener">EXTRACT()</a></td>
<td align="left">返回日期/时间按的单独部分</td>
</tr>
<tr>
<td align="left"><a href="https://www.w3school.com.cn/sql/func_date_add.asp" target="_blank" rel="noopener">DATE_ADD()</a></td>
<td align="left">给日期添加指定的时间间隔</td>
</tr>
<tr>
<td align="left"><a href="https://www.w3school.com.cn/sql/func_date_sub.asp" target="_blank" rel="noopener">DATE_SUB()</a></td>
<td align="left">从日期减去指定的时间间隔</td>
</tr>
<tr>
<td align="left"><a href="https://www.w3school.com.cn/sql/func_datediff_mysql.asp" target="_blank" rel="noopener">DATEDIFF()</a></td>
<td align="left">返回两个日期之间的天数</td>
</tr>
<tr>
<td align="left"><a href="https://www.w3school.com.cn/sql/func_date_format.asp" target="_blank" rel="noopener">DATE_FORMAT()</a></td>
<td align="left">用不同的格式显示日期/时间</td>
</tr>
</tbody></table>
<h4 id="3-3-数值处理函数"><a href="#3-3-数值处理函数" class="headerlink" title="3.3 数值处理函数"></a>3.3 数值处理函数</h4><table>
<thead>
<tr>
<th>函数</th>
<th>描述</th>
</tr>
</thead>
<tbody><tr>
<td>ABS(X)</td>
<td>求绝对值</td>
</tr>
<tr>
<td>Ceil(x)</td>
<td>大于x的最大整数</td>
</tr>
<tr>
<td>Floor(x)</td>
<td>小于x的最大整数</td>
</tr>
<tr>
<td>Mod(x,y)</td>
<td>x/y的模</td>
</tr>
<tr>
<td>Rand()</td>
<td>0-1内的随机值</td>
</tr>
<tr>
<td>Rand(x,y)</td>
<td>返回参数x的四舍五入的有y位小数的值</td>
</tr>
<tr>
<td>Truncate(x,y)</td>
<td>返回数字x截断为y位小数的结果</td>
</tr>
</tbody></table>
<h3 id="4-汇总数据"><a href="#4-汇总数据" class="headerlink" title="4. 汇总数据"></a>4. 汇总数据</h3><h4 id="4-1-聚集函数"><a href="#4-1-聚集函数" class="headerlink" title="4.1 聚集函数"></a>4.1 聚集函数</h4><table>
<thead>
<tr>
<th>函 数</th>
<th>说 明</th>
</tr>
</thead>
<tbody><tr>
<td>AVG()</td>
<td>返回某列的平均值</td>
</tr>
<tr>
<td>COUNT()</td>
<td>返回某列的行数</td>
</tr>
<tr>
<td>MAX()</td>
<td>返回某列的最大值</td>
</tr>
<tr>
<td>MIN()</td>
<td>返回某列的最小值</td>
</tr>
<tr>
<td>SUM()</td>
<td>返回某列值之和</td>
</tr>
</tbody></table>
<ol>
<li><p>AVG()函数</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT AVG(prod_price) AS avg_price FROM Products;</span><br></pre></td></tr></table></figure>

<blockquote>
<ul>
<li><strong>只能用于单个列，多列需要多个AVG函数</strong></li>
<li><strong>AVG()忽略值为NULL的行</strong></li>
</ul>
</blockquote>
</li>
<li><p>COUNT()函数</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT COUNT(cust_email) as num_cust FROM Customers;</span><br></pre></td></tr></table></figure>

<blockquote>
<p><strong>NULL值</strong> 如果指定列名，则指定列的值为空会被COUNT忽略，但是如果是COUNT(*),则不忽略。 </p>
</blockquote>
</li>
<li><p>MAX()/MIN() 数值时返回的时列最大值/最小值，文本则返回首条记录/最后一条记录</p>
<blockquote>
<p><strong>NULL值</strong> MIN()函数忽略值为NULL的行</p>
</blockquote>
</li>
<li><p>SUM求和</p>
<blockquote>
<p><strong>NULL值</strong> sum()函数忽略值为NULL的行</p>
</blockquote>
</li>
</ol>
<h3 id="5-分组查询"><a href="#5-分组查询" class="headerlink" title="5. 分组查询"></a>5. 分组查询</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT vend_id, count(*) AS num_prods FROM Products GROUP BY vend_id;</span><br></pre></td></tr></table></figure>

<blockquote>
<ul>
<li>如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。</li>
<li>GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。</li>
</ul>
</blockquote>
<h4 id="5-1-过滤分组"><a href="#5-1-过滤分组" class="headerlink" title="5.1 过滤分组"></a>5.1 过滤分组</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT cust_id, COUNT(*) as orders FROM Orders GROUP BY cust_id HAVING COUNT(*) &gt;&#x3D; 2;</span><br></pre></td></tr></table></figure>

<p><strong>HAVING与WHERE的区别：</strong>where在数据分组前过滤，HAVING在数据分组后过滤。</p>
<h3 id="6-连结表"><a href="#6-连结表" class="headerlink" title="6. 连结表"></a>6. 连结表</h3><p>关联供应商表和产品表</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT vend_name, prod_name, prod_price FROM Vendors, Products WHERE Vendors.vend_id&#x3D;Products.vend_id ORDER BY vend_name, prod_name;</span><br></pre></td></tr></table></figure>

<h4 id="6-1-内部连结"><a href="#6-1-内部连结" class="headerlink" title="6.1 内部连结"></a>6.1 内部连结</h4><p>基于两个表之间的相等测试，这种连结成为<strong>内部连结</strong>，上面的例子也是。不过有更规范的语法来指明内连结：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT vend_name, prod_name, prod_price FROM Vendors INNER JOIN Products ON Vendors.vend_id&#x3D;Products.vend_id ORDER BY vend_name, prod_name; # xx INNER JOIN yy ON xx.id &#x3D; yy.id</span><br></pre></td></tr></table></figure>

<h4 id="6-2-高级连结"><a href="#6-2-高级连结" class="headerlink" title="6.2 高级连结"></a>6.2 高级连结</h4><h5 id="6-2-1-自联结"><a href="#6-2-1-自联结" class="headerlink" title="6.2.1 自联结"></a>6.2.1 自联结</h5><p>假如你发现某物品（其ID为DTNTR）存在问题，因此想知道生产该物品的供应商生产的其他物品是否也存在这些问题。此查询要首先找到生产ID为DTNTR的物品的供应商，然后找出这个供应商生产的其他物品。下面是解决此问题的一种方法</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT prod_id, prod_name FROM products where vend_id&#x3D;(SELECT vend_id FROM products WHERE prod_id &#x3D; &#39;DTNTR&#39;);</span><br></pre></td></tr></table></figure>

<p>用自联结实现呢？</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT p1.prod_name, p1.prod_id FROM Products as p1, Products as p2 WHERE p1.vend_id &#x3D; p2.vend_id AND p2.prod_id &#x3D; &quot;BR01&quot;;</span><br></pre></td></tr></table></figure>

<p>这里面时供应商ID做连结，多值连结。连结后的表同一个供应商有 count(vend_id)^2个，通过另一个prod_id即可找到所有同一个供应商提供的产品编号。</p>
<h5 id="6-2-1-自然连结"><a href="#6-2-1-自然连结" class="headerlink" title="6.2.1 自然连结"></a>6.2.1 自然连结</h5><p>出现重复列的时候，只保留一列。这就需要我们手动实现。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"># 保留C中全部，其余手动给出</span><br><span class="line">SELECT c.*, o.order_num, o.cust_id,oi.prod_id,oi.quantity,oi.item_price FROM Customers as c, Orders as o, OrderItems as oi WHERE c.cust_id &#x3D; o.cust_id and oi.order_num &#x3D; o.order_num and prod_id &#x3D; &#39;BNBG01&#39;;</span><br></pre></td></tr></table></figure>

<h5 id="6-2-3-外部连结"><a href="#6-2-3-外部连结" class="headerlink" title="6.2.3 外部连结"></a>6.2.3 外部连结</h5><p>如果两张表在关联时，存在无法对应的关系，内部关联或等值关联是不会保留无法对应的行，但有时候需要保留没有关联的那些行。</p>
<ul>
<li>LEFT OUTER JOIN: 无法匹配保留左表的值，右表对应的值置为NULL</li>
<li>RIGHT OUTER JOIN:无法匹配保留由表的值，右表对应的值置为NULL</li>
</ul>
<blockquote>
<p>存在两种基本的外部联结形式：左外部联结和右外部联结。它们之间的唯一差别是所关联的表的顺序不同。换句话说，左外部联结可通过颠倒FROM或WHERE子句中</p>
</blockquote>
<h3 id="7-组合查询"><a href="#7-组合查询" class="headerlink" title="7. 组合查询"></a>7. 组合查询</h3><p>利用UNION操作符可以将多条SELECT语句组合成一个结果集。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">SELECT vend_id,  prod_price FROM Products WHERE prod_price &lt;&#x3D; 5</span><br><span class="line">UNION</span><br><span class="line">SELECT vend_id,  prod_price FROM Products WHERE prod_price &gt;&#x3D; 10;</span><br></pre></td></tr></table></figure>

<blockquote>
<p>UNION 会自动去除重复的行, 如果想保留的话,就是用 UNION ALL</p>
</blockquote>
<h3 id="8-全文搜索"><a href="#8-全文搜索" class="headerlink" title="8. 全文搜索"></a>8. 全文搜索</h3><p>其实MySQL也是支持全文搜索的,只是InnoDB引擎不支持,而MyISAM是支持的.但是没人用MySQL做搜索,自行了解其他的吧.如ES,Solr</p>
<h2 id="二、插入数据（INSERT）"><a href="#二、插入数据（INSERT）" class="headerlink" title="二、插入数据（INSERT）"></a>二、插入数据（INSERT）</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">INSERT INTO 表名(字段名1, 字段名2,...) VALUES(值1, 值2),(值1, 值2); # 向表中插入俩条数据</span><br></pre></td></tr></table></figure>

<h4 id="1-插入检索出的数据"><a href="#1-插入检索出的数据" class="headerlink" title="1. 插入检索出的数据"></a>1. 插入检索出的数据</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">INSERT INTO XX(aa,bb,cc) SELECT aa,bb,cc FROM XXNew Where zz&#x3D;j;</span><br></pre></td></tr></table></figure>

<h2 id="三、更新和删除-UPDATE-amp-DROP"><a href="#三、更新和删除-UPDATE-amp-DROP" class="headerlink" title="三、更新和删除(UPDATE &amp; DROP)"></a>三、更新和删除(UPDATE &amp; DROP)</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">UPDATE table_name SET field1&#x3D;new-value1, field2&#x3D;new-value2 [WHERE Clause]</span><br></pre></td></tr></table></figure>

<ul>
<li>你可以同时更新一个或多个字段</li>
<li>你可以在 WHERE 子句中指定任何条件</li>
<li>你可以在一个单独表中同时更新数据</li>
</ul>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">UPDATE Customers SET cust_zip&#x3D;8877, cust_name&#x3D;&#39;hito&#39; WHERE cust_state &#x3D; &#39;AZ;</span><br></pre></td></tr></table></figure>

<h3 id="1-删除"><a href="#1-删除" class="headerlink" title="1. 删除"></a>1. 删除</h3><h4 id="1-1-删除表"><a href="#1-1-删除表" class="headerlink" title="1.1 删除表"></a>1.1 删除表</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">DROP TABLE table_name ;</span><br></pre></td></tr></table></figure>

<h4 id="1-2-删除行"><a href="#1-2-删除行" class="headerlink" title="1.2 删除行"></a>1.2 删除行</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">DELETE FROM Vendors WHERE vend_id &#x3D; 89757;</span><br></pre></td></tr></table></figure>

<p>注意还有个清空表的操作 ,注意这比一行行删除快，但是不可恢复，因为它是清空表后重建。<a href="https://dev.mysql.com/doc/refman/8.0/en/truncate-table.html" target="_blank" rel="noopener">具体注意事项见官网</a></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">TRUNCATE TABLE XX;</span><br></pre></td></tr></table></figure>

<h4 id="1-3-删除数据库"><a href="#1-3-删除数据库" class="headerlink" title="1.3 删除数据库"></a>1.3 删除数据库</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">drop database &lt;数据库名&gt;;</span><br></pre></td></tr></table></figure>

<h2 id="四、创建表"><a href="#四、创建表" class="headerlink" title="四、创建表"></a>四、创建表</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">CREATE TABLE table_name (column_name column_type);</span><br></pre></td></tr></table></figure>

<p>引擎</p>
<ol>
<li>InnoDB：可靠的事务处理引擎</li>
<li>MEMORY：功能等同于MyISAM，但是数据存储在内存，速度快，适合临时表</li>
<li>MyISAM：高性能的、支持全文搜索的引擎，但不支持事务</li>
</ol>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">CREATE TABLE IF NOT EXISTS &#96;runoob_tbl&#96;(</span><br><span class="line">   &#96;runoob_id&#96; INT UNSIGNED AUTO_INCREMENT,</span><br><span class="line">   &#96;runoob_title&#96; VARCHAR(100) NOT NULL,</span><br><span class="line">   &#96;runoob_author&#96; VARCHAR(40) NOT NULL,</span><br><span class="line">   &#96;submission_date&#96; DATE DEFAULT &#39;2019-09-21&#39;,</span><br><span class="line">   PRIMARY KEY ( &#96;runoob_id&#96; )</span><br><span class="line">)ENGINE&#x3D;InnoDB DEFAULT CHARSET&#x3D;utf8;</span><br></pre></td></tr></table></figure>

<ul>
<li>如果你不想字段为 <strong>NULL</strong> 可以设置字段的属性为 <strong>NOT NULL</strong>， 在操作数据库时如果输入该字段的数据为<strong>NULL</strong> ，就会报错。</li>
<li>AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。</li>
<li>PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。</li>
<li>ENGINE 设置存储引擎，CHARSET 设置编码。</li>
</ul>
<p>PS: <a href="https://www.vertabelo.com/blog/what-datatype-should-you-use-to-represent-time-in-mysql-we-compare-datetime-timestamp-and-int/" target="_blank" rel="noopener">关于DATETIME和TIMESTAMP</a></p>
<h3 id="1-修改表"><a href="#1-修改表" class="headerlink" title="1. 修改表"></a>1. 修改表</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">ALERT TABLE 表名 ADD COLUMN 字段名 属性...;</span><br></pre></td></tr></table></figure>

<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">ALERT TABLE 表名 DROP COLUMN 字段名;</span><br></pre></td></tr></table></figure>

<h2 id="五、视图"><a href="#五、视图" class="headerlink" title="五、视图"></a>五、视图</h2><p>将一些复杂的、常用的SQL语句封装起来，其结果可以看作一张“实际”存在的表来操作，这张表就是视图。创建View的语句：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">CREATE VIEW view_name AS (需要封装的语句);</span><br></pre></td></tr></table></figure>

<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">CREATE VIEW testView AS</span><br><span class="line">SELECT vend_id,  prod_price FROM Products WHERE prod_price &lt;&#x3D; 5</span><br><span class="line">UNION</span><br><span class="line">SELECT vend_id,  prod_price FROM Products WHERE prod_price &gt;&#x3D; 10;</span><br><span class="line"># 只需要查询视图数据即可</span><br><span class="line">SELECT * From testView;</span><br></pre></td></tr></table></figure>

<p><strong>那么直接修改视图的话，原表会得到更新吗？</strong></p>
<ul>
<li>删除视图：不会删除原表</li>
<li>对视图进行更新操作：如果由修改权限，并且更新合法，是可以更新原表的。</li>
</ul>
<h2 id="六、存储过程"><a href="#六、存储过程" class="headerlink" title="六、存储过程"></a>六、存储过程</h2><p>存储过程也是一种SQL语句的封装，但是自定义程度更高，接受入参并能返回结果，而且相比较组合执行SQL是语句性能更高。</p>
<h3 id="1-创建存储过程"><a href="#1-创建存储过程" class="headerlink" title="1. 创建存储过程"></a>1. 创建存储过程</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">CREATE</span><br><span class="line">    [DEFINER &#x3D; user]</span><br><span class="line">    PROCEDURE sp_name ([proc_parameter[,...]])</span><br><span class="line">    [characteristic ...] routine_body</span><br><span class="line"># proc_parameter: [ IN | OUT | INOUT ] param_name type</span><br></pre></td></tr></table></figure>

<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"># 创建，接收一个用来装返回值的参数</span><br><span class="line">CREATE PROCEDURE productpricing2(OUT avg_p DOUBLE)</span><br><span class="line">BEGIN</span><br><span class="line">	SELECT Avg(prod_price) INTO avg_p</span><br><span class="line">	FROM Products;</span><br><span class="line">END;</span><br><span class="line"># 调用存储过程后，查看结果</span><br><span class="line">CALL productpricing2(@lala);</span><br><span class="line">SELECT @lala;</span><br></pre></td></tr></table></figure>

<p>你需要获得与以前一样的订单合计，但需要对合计增加营业税，不过只针对某些顾客（或许是你所在州中那些顾客）。那么，<br>你需要做下面几件事情： </p>
<ul>
<li>获得合计（与以前一样）； </li>
<li>把营业税有条件地添加到合计； </li>
<li>返回合计（带或不带税）</li>
</ul>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br></pre></td><td class="code"><pre><span class="line">#Name:ordertotal</span><br><span class="line">#Parameters:onumber&#x3D;order number</span><br><span class="line">#                     taxable&#x3D;0 if not taxable,1 if taxable</span><br><span class="line">#                     ototal&#x3D;order total variable</span><br><span class="line">CREATE PROCEDURE ordertotal(</span><br><span class="line">    IN onumber INT,</span><br><span class="line">    IN taxable BOOLEAN,</span><br><span class="line">    OUT ototal DECIMAL(8,2)</span><br><span class="line">)COMMENT &#39;Obtain order total,optionally adding tax&#39;</span><br><span class="line">BEGIN</span><br><span class="line">    #Declare variable for total</span><br><span class="line">    DECLARE total DECIMAL(8,2);</span><br><span class="line">    #Declare tax percentage</span><br><span class="line">    DECLARE taxrate INT DEFAULT 6;</span><br><span class="line">    #Get the order total</span><br><span class="line">    SELECT Sum(item_price*quantity)</span><br><span class="line">    FROM orderitems</span><br><span class="line">    WHERE order_num&#x3D;onumber</span><br><span class="line">    INTO total;</span><br><span class="line">    #Is this taxable?</span><br><span class="line">    IF taxable THEN</span><br><span class="line">            #Yes,so add taxrate to the total</span><br><span class="line">        SELECT total+(total&#x2F;100*taxrate)INTO total;</span><br><span class="line">    END IF;</span><br><span class="line">    # AND finally,save to out variable</span><br><span class="line">    SELECT total INTO ototal;</span><br><span class="line">END;</span><br></pre></td></tr></table></figure>

<h2 id="七、使用游标-TODO"><a href="#七、使用游标-TODO" class="headerlink" title="七、使用游标(TODO)"></a>七、使用游标(TODO)</h2><h2 id="八、触发器"><a href="#八、触发器" class="headerlink" title="八、触发器"></a>八、触发器</h2>
      
    </div>
    
    
    

    

    

    

    <footer class="post-footer">
      
        <div class="post-tags">
          
            <a href="/java-notes/tags/sql/" rel="tag"># sql</a>
          
        </div>
      

      
      
      

      
        <div class="post-nav">
          <div class="post-nav-next post-nav-item">
            
              <a href="/java-notes/2020/04/09/MySQL%E7%9F%A5%E8%AF%86%E7%82%B9/" rel="next" title="MySQL知识点">
                <i class="fa fa-chevron-left"></i> MySQL知识点
              </a>
            
          </div>

          <span class="post-nav-divider"></span>

          <div class="post-nav-prev post-nav-item">
            
              <a href="/java-notes/2020/04/19/Java%E5%B9%B6%E5%8F%91%E7%BC%96%E7%A8%8B/" rel="prev" title="Java并发编程(原理)">
                Java并发编程(原理) <i class="fa fa-chevron-right"></i>
              </a>
            
          </div>
        </div>
      

      
      
    </footer>
  </div>
  
  
  
  </article>



    <div class="post-spread">
      
    </div>
  </div>


          </div>
          


          

  



        </div>
        
          
  
  <div class="sidebar-toggle">
    <div class="sidebar-toggle-line-wrap">
      <span class="sidebar-toggle-line sidebar-toggle-line-first"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-middle"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-last"></span>
    </div>
  </div>

  <aside id="sidebar" class="sidebar">
    
    <div class="sidebar-inner">

      

      
        <ul class="sidebar-nav motion-element">
          <li class="sidebar-nav-toc sidebar-nav-active" data-target="post-toc-wrap">
            Table of Contents
          </li>
          <li class="sidebar-nav-overview" data-target="site-overview-wrap">
            Overview
          </li>
        </ul>
      

      <section class="site-overview-wrap sidebar-panel">
        <div class="site-overview">
          <div class="site-author motion-element" itemprop="author" itemscope itemtype="http://schema.org/Person">
            
              <p class="site-author-name" itemprop="name">HitoMeng</p>
              <p class="site-description motion-element" itemprop="description">You do have the time!</p>
          </div>

          <nav class="site-state motion-element">

            
              <div class="site-state-item site-state-posts">
              
                <a href="/java-notes/archives/%7C%7Carchive">
              
                  <span class="site-state-item-count">7</span>
                  <span class="site-state-item-name">posts</span>
                </a>
              </div>
            

            
              
              
              <div class="site-state-item site-state-categories">
                <a href="/java-notes/categories/index.html">
                  <span class="site-state-item-count">3</span>
                  <span class="site-state-item-name">categories</span>
                </a>
              </div>
            

            
              
              
              <div class="site-state-item site-state-tags">
                <a href="/java-notes/tags/index.html">
                  <span class="site-state-item-count">8</span>
                  <span class="site-state-item-name">tags</span>
                </a>
              </div>
            

          </nav>

          

          

          
          

          
          

          

        </div>
      </section>

      
      <!--noindex-->
        <section class="post-toc-wrap motion-element sidebar-panel sidebar-panel-active">
          <div class="post-toc">

            
              
            

            
              <div class="post-toc-content"><ol class="nav"><li class="nav-item nav-level-1"><a class="nav-link" href="#MySQL必知必会"><span class="nav-number">1.</span> <span class="nav-text">MySQL必知必会</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#1-SELECT语句"><span class="nav-number">1.0.1.</span> <span class="nav-text">1. SELECT语句</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#1-1-检索单个列"><span class="nav-number">1.0.1.1.</span> <span class="nav-text">1.1 检索单个列</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#1-2-检索多个列"><span class="nav-number">1.0.1.2.</span> <span class="nav-text">1.2 检索多个列</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#1-3-限制检索结果"><span class="nav-number">1.0.1.3.</span> <span class="nav-text">1.3 限制检索结果</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#1-4-排序检索结果"><span class="nav-number">1.0.1.4.</span> <span class="nav-text">1.4 排序检索结果</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#1-5-过滤数据"><span class="nav-number">1.0.1.5.</span> <span class="nav-text">1.5 过滤数据</span></a><ol class="nav-child"><li class="nav-item nav-level-5"><a class="nav-link" href="#1-5-1-检查单个值"><span class="nav-number">1.0.1.5.1.</span> <span class="nav-text">1.5.1 检查单个值</span></a></li><li class="nav-item nav-level-5"><a class="nav-link" href="#1-5-2-范围检查"><span class="nav-number">1.0.1.5.2.</span> <span class="nav-text">1.5.2 范围检查</span></a></li><li class="nav-item nav-level-5"><a class="nav-link" href="#1-5-3-控制查询"><span class="nav-number">1.0.1.5.3.</span> <span class="nav-text">1.5.3 控制查询</span></a></li><li class="nav-item nav-level-5"><a class="nav-link" href="#1-5-4-OR操作符"><span class="nav-number">1.0.1.5.4.</span> <span class="nav-text">1.5.4 OR操作符</span></a></li><li class="nav-item nav-level-5"><a class="nav-link" href="#1-5-5-IN-操作符"><span class="nav-number">1.0.1.5.5.</span> <span class="nav-text">1.5.5 IN 操作符</span></a></li></ol></li><li class="nav-item nav-level-4"><a class="nav-link" href="#1-6-用通配符进行过滤"><span class="nav-number">1.0.1.6.</span> <span class="nav-text">1.6 用通配符进行过滤</span></a><ol class="nav-child"><li class="nav-item nav-level-5"><a class="nav-link" href="#1-6-1-LIKE操作符"><span class="nav-number">1.0.1.6.1.</span> <span class="nav-text">1.6.1 LIKE操作符</span></a></li></ol></li><li class="nav-item nav-level-4"><a class="nav-link" href="#1-7-使用正则表达式搜索"><span class="nav-number">1.0.1.7.</span> <span class="nav-text">1.7 使用正则表达式搜索</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#2-创建计算字段"><span class="nav-number">1.0.2.</span> <span class="nav-text">2. 创建计算字段</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#2-1-拼接字段"><span class="nav-number">1.0.2.1.</span> <span class="nav-text">2.1 拼接字段</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#2-2-去除空格"><span class="nav-number">1.0.2.2.</span> <span class="nav-text">2.2 去除空格</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#2-3-执行算术计算"><span class="nav-number">1.0.2.3.</span> <span class="nav-text">2.3 执行算术计算</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#3-数据处理函数"><span class="nav-number">1.0.3.</span> <span class="nav-text">3. 数据处理函数</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#3-1-字符串处理工具"><span class="nav-number">1.0.3.1.</span> <span class="nav-text">3.1 字符串处理工具</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#3-2-日期和时间处理函数"><span class="nav-number">1.0.3.2.</span> <span class="nav-text">3.2 日期和时间处理函数</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#3-3-数值处理函数"><span class="nav-number">1.0.3.3.</span> <span class="nav-text">3.3 数值处理函数</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#4-汇总数据"><span class="nav-number">1.0.4.</span> <span class="nav-text">4. 汇总数据</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#4-1-聚集函数"><span class="nav-number">1.0.4.1.</span> <span class="nav-text">4.1 聚集函数</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#5-分组查询"><span class="nav-number">1.0.5.</span> <span class="nav-text">5. 分组查询</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#5-1-过滤分组"><span class="nav-number">1.0.5.1.</span> <span class="nav-text">5.1 过滤分组</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#6-连结表"><span class="nav-number">1.0.6.</span> <span class="nav-text">6. 连结表</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#6-1-内部连结"><span class="nav-number">1.0.6.1.</span> <span class="nav-text">6.1 内部连结</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#6-2-高级连结"><span class="nav-number">1.0.6.2.</span> <span class="nav-text">6.2 高级连结</span></a><ol class="nav-child"><li class="nav-item nav-level-5"><a class="nav-link" href="#6-2-1-自联结"><span class="nav-number">1.0.6.2.1.</span> <span class="nav-text">6.2.1 自联结</span></a></li><li class="nav-item nav-level-5"><a class="nav-link" href="#6-2-1-自然连结"><span class="nav-number">1.0.6.2.2.</span> <span class="nav-text">6.2.1 自然连结</span></a></li><li class="nav-item nav-level-5"><a class="nav-link" href="#6-2-3-外部连结"><span class="nav-number">1.0.6.2.3.</span> <span class="nav-text">6.2.3 外部连结</span></a></li></ol></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#7-组合查询"><span class="nav-number">1.0.7.</span> <span class="nav-text">7. 组合查询</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#8-全文搜索"><span class="nav-number">1.0.8.</span> <span class="nav-text">8. 全文搜索</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#二、插入数据（INSERT）"><span class="nav-number">1.1.</span> <span class="nav-text">二、插入数据（INSERT）</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#1-插入检索出的数据"><span class="nav-number">1.1.0.1.</span> <span class="nav-text">1. 插入检索出的数据</span></a></li></ol></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#三、更新和删除-UPDATE-amp-DROP"><span class="nav-number">1.2.</span> <span class="nav-text">三、更新和删除(UPDATE &amp; DROP)</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#1-删除"><span class="nav-number">1.2.1.</span> <span class="nav-text">1. 删除</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#1-1-删除表"><span class="nav-number">1.2.1.1.</span> <span class="nav-text">1.1 删除表</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#1-2-删除行"><span class="nav-number">1.2.1.2.</span> <span class="nav-text">1.2 删除行</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#1-3-删除数据库"><span class="nav-number">1.2.1.3.</span> <span class="nav-text">1.3 删除数据库</span></a></li></ol></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#四、创建表"><span class="nav-number">1.3.</span> <span class="nav-text">四、创建表</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#1-修改表"><span class="nav-number">1.3.1.</span> <span class="nav-text">1. 修改表</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#五、视图"><span class="nav-number">1.4.</span> <span class="nav-text">五、视图</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#六、存储过程"><span class="nav-number">1.5.</span> <span class="nav-text">六、存储过程</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#1-创建存储过程"><span class="nav-number">1.5.1.</span> <span class="nav-text">1. 创建存储过程</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#七、使用游标-TODO"><span class="nav-number">1.6.</span> <span class="nav-text">七、使用游标(TODO)</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#八、触发器"><span class="nav-number">1.7.</span> <span class="nav-text">八、触发器</span></a></li></ol></li></ol></div>
            

          </div>
        </section>
      <!--/noindex-->
      

      

    </div>
  </aside>


        
      </div>
    </main>

    <footer id="footer" class="footer">
      <div class="footer-inner">
        <div class="copyright">&copy; <span itemprop="copyrightYear">2020</span>
  <span class="with-love">
    <i class="fa fa-user"></i>
  </span>
  <span class="author" itemprop="copyrightHolder">HitoMeng</span>

  
</div>


  <div class="powered-by">Powered by <a class="theme-link" target="_blank" href="https://hexo.io">Hexo</a></div>



  <span class="post-meta-divider">|</span>



  <div class="theme-info">Theme &mdash; <a class="theme-link" target="_blank" href="https://github.com/iissnan/hexo-theme-next">NexT.Gemini</a> v5.1.4</div>




        







        
      </div>
    </footer>

    
      <div class="back-to-top">
        <i class="fa fa-arrow-up"></i>
        
      </div>
    

    

  </div>

  

<script type="text/javascript">
  if (Object.prototype.toString.call(window.Promise) !== '[object Function]') {
    window.Promise = null;
  }
</script>









  












  
  
    <script type="text/javascript" src="/java-notes/lib/jquery/index.js?v=2.1.3"></script>
  

  
  
    <script type="text/javascript" src="/java-notes/lib/fastclick/lib/fastclick.min.js?v=1.0.6"></script>
  

  
  
    <script type="text/javascript" src="/java-notes/lib/jquery_lazyload/jquery.lazyload.js?v=1.9.7"></script>
  

  
  
    <script type="text/javascript" src="/java-notes/lib/velocity/velocity.min.js?v=1.2.1"></script>
  

  
  
    <script type="text/javascript" src="/java-notes/lib/velocity/velocity.ui.min.js?v=1.2.1"></script>
  

  
  
    <script type="text/javascript" src="/java-notes/lib/fancybox/source/jquery.fancybox.pack.js?v=2.1.5"></script>
  


  


  <script type="text/javascript" src="/java-notes/js/src/utils.js?v=5.1.4"></script>

  <script type="text/javascript" src="/java-notes/js/src/motion.js?v=5.1.4"></script>



  
  


  <script type="text/javascript" src="/java-notes/js/src/affix.js?v=5.1.4"></script>

  <script type="text/javascript" src="/java-notes/js/src/schemes/pisces.js?v=5.1.4"></script>



  
  <script type="text/javascript" src="/java-notes/js/src/scrollspy.js?v=5.1.4"></script>
<script type="text/javascript" src="/java-notes/js/src/post-details.js?v=5.1.4"></script>



  


  <script type="text/javascript" src="/java-notes/js/src/bootstrap.js?v=5.1.4"></script>



  


  




	





  





  












  





  

  

  

  
  

  

  

  

</body>
</html>
